package Model;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import POJO.ProductPOJO;

public class ProductModel {
	public static ArrayList<ProductPOJO> DanhSachMatHang()
	{
	     Connection con = DataProvider.Connection();
	     ProductPOJO product=null;
	     ArrayList<ProductPOJO> listProduct= new  ArrayList<ProductPOJO>();
	     if(con != null)
	     {
	    	  try {
	                PreparedStatement ps;
	                ps  = con.prepareCall("select * from [product]");
	                ResultSet rs = ps.executeQuery();
	                while( rs.next()) {
	                	product = new ProductPOJO();
	                	product.SetID(rs.getInt("id"));
	                	product.SetName(rs.getString("name"));
	                	product.SetPrice(rs.getFloat("price"));
	                	product.SetNumber(rs.getInt("number"));
	                	product.SetCategory(rs.getInt("category_id"));
	                	product.SetInformation(rs.getString("information"));
	                	product.SetImage(rs.getString("image"));
	                	listProduct.add(product);
	                }
	                con.close();
	            } catch (SQLException ex) {
	                return null;
	            }    
	     }
	     return listProduct;
	}
	public static ArrayList<ProductPOJO> TimKiem(String Ser) throws UnsupportedEncodingException
	{
		Connection con = DataProvider.Connection();
		 ProductPOJO product=null;
	     ArrayList<ProductPOJO> listProduct= new  ArrayList<ProductPOJO>();
		 if(con!=null)
		 {
	    	  try {
	                PreparedStatement ps;
	                if(Ser != "")
	                {
	               
	                ps  = con.prepareCall("select * from [product] where name Like ? ");
	                byte pname[]=Ser.getBytes("ISO-8859-1");
	                String value1= new String(pname,"UTF-8");
	                value1 = '%' + value1 + '%';
	                ps.setString(1,value1);
	                }
	                else
	                {
	                	 ps  = con.prepareCall("select * from [product] ");
	                }
	                ResultSet rs = ps.executeQuery();
	                while( rs.next()) {
	                	product = new ProductPOJO();
	                	product.SetID(rs.getInt("id"));
	                	product.SetName(rs.getString("name"));
	                	product.SetPrice(rs.getFloat("price"));
	                	product.SetNumber(rs.getInt("number"));
	                	product.SetCategory(rs.getInt("category_id"));
	                	product.SetInformation(rs.getString("information"));
	                	product.SetImage(rs.getString("image"));
	                	listProduct.add(product);
	                }
	                con.close();
	            } catch (SQLException ex) {
	                return null;
	            }    
		 }
	     return listProduct;
	}
	public static ProductPOJO SelectProductByID(String ID)
	{
		  Connection con = DataProvider.Connection();
		     ProductPOJO product=null;
		     if(con != null)
		     {
		    	  try {
		                PreparedStatement ps;
		                ps  = con.prepareCall("select * from [product] where id=?");
		                ps.setString(1,ID);
		                ResultSet rs = ps.executeQuery();
		                while( rs.next()) {
		                	product = new ProductPOJO();
		                	product.SetID(rs.getInt("id"));
		                	product.SetName(rs.getString("name"));
		                	product.SetPrice(rs.getFloat("price"));
		                	product.SetNumber(rs.getInt("number"));
		                	product.SetCategory(rs.getInt("category_id"));
		                	product.SetInformation(rs.getString("information"));
		                	product.SetImage(rs.getString("image"));
		                	
		                }
		                con.close();
		            } catch (SQLException ex) {
		                return null;
		            }    
		     }
		     return product;
	}
	public static int InsertProduct(String name,float price,int number,int category_id,String information) throws UnsupportedEncodingException
	{
		int flag=0;
		Connection con = DataProvider.Connection();
		if(con!=null)
		{
			  try {
	                PreparedStatement ps;
	                ps  = con.prepareCall("Insert into [product] values(?,?,?,?,?,?)");
	                byte pname[]=name.getBytes("ISO-8859-1");
	                String value1=new String(pname,"UTF-8");
	                ps.setString(1,value1);
	                //
	        
	                ps.setFloat(2,price);
	                ps.setInt(3,number);
	                ps.setInt(4, category_id);
	                byte pinfo[]=information.getBytes("ISO-8859-1");
	                String value2=new String(pinfo,"UTF-8");
	                ps.setString(5,value2);
	                //
	                ps.setString(6,"");
	                flag = ps.executeUpdate();              
	                con.close();
	            } catch (SQLException ex) {
	            	ex.printStackTrace();
	            }    
		}
		return flag;
	}
	public static int UpdateProduct(String name,float price,int number,int category_id,String information,String ID) throws UnsupportedEncodingException
	{
		int flag=0;
		Connection con = DataProvider.Connection();
		if(con!=null)
		{
			  try {
	                PreparedStatement ps;
	                ps  = con.prepareCall("Update [product] set [name]=?,price=?,number=?,category_id=?,information=?,[image]=? where id=?");
	                byte pname[]=name.getBytes("ISO-8859-1");
	                String value1=new String(pname,"UTF-8");
	                ps.setString(1,value1);
	                //
	                ps.setFloat(2,price);
	                ps.setInt(3,number);
	                ps.setInt(4, category_id);
	                //
	                byte pinfo[]=information.getBytes("ISO-8859-1");
	                String value2=new String(pinfo,"UTF-8");
	                ps.setString(5,value2);	
	                //
	                ps.setString(5,information);
	                ps.setString(6,"");
	                ps.setString(7,ID);
	                flag = ps.executeUpdate();              
	                con.close();
	            } catch (SQLException ex) {
	            	ex.printStackTrace();
	            }    
		}
		return flag;
	}
	public static int DeleteProduct(int id)
	{
		int flag=0;
		Connection con = DataProvider.Connection();
		if(con!=null)
		{
			  try {
	                PreparedStatement ps;
	                ps  = con.prepareCall("Delete from [product] where id=?");
	                ps.setInt(1, id);
	                flag = ps.executeUpdate();              
	                con.close();
	            } catch (SQLException ex) {
	            	ex.printStackTrace();
	            }    
		}
		return flag;
	}
}
